package com.tuanzi.loan.business.repository.family.impl;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanApiRecord.LOAN_API_RECORD;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import com.tuanzi.loan.business.repository.family.custom.LoanApiRecordRepositoryCustom;
import com.tuanzi.loan.core.repository.JOOQBaseRepository;
import com.tuanzi.loan.vo.request.jiafang.NuanXinDaiUnionSearchRequest;
import com.tuanzi.loan.vo.response.jiafang.NuanXinDaiUnionSearchResponse;
import com.tuanzi.server.loan_base.util.DateUtils;

/**
 * @author chenweijun 2017/11/08
 */
@SuppressWarnings({ "rawtypes" })
public class LoanApiRecordRepositoryImpl extends JOOQBaseRepository implements LoanApiRecordRepositoryCustom {

	@Override
	protected TableImpl getTable() {
		return LOAN_API_RECORD;
	}

	@Override
	public Page<NuanXinDaiUnionSearchResponse> statNuanXinDaiUnion(NuanXinDaiUnionSearchRequest searchRequest, Pageable pageable) {

		List<Object> paramList = new ArrayList<Object>();

		StringBuilder sql = new StringBuilder("SELECT `type` AS unionName, DATE(ctime) AS createDate, COUNT(DISTINCT(mobnum)) AS unionRequestNum, IFNULL(b.unionSuccessNum,0) AS unionSuccessNum FROM `loan_api_record` AS a")
		.append(" LEFT JOIN(")
		.append(" SELECT DATE(ctime) AS cdate, COUNT(DISTINCT(mobnum)) AS unionSuccessNum FROM `loan_api_record` WHERE `type` = 15 AND `result` = 1 AND `first_union` = 1 GROUP BY cdate")
		.append(" ) AS b ON DATE(ctime)  = cdate")
		.append(" WHERE `type` = 15 ");
		
		if(searchRequest.getStartDate() != null){
			sql.append(" AND DATE(`ctime`) >= ?");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getStartDate()));
		}
		
		if(searchRequest.getEndDate() != null){
			sql.append(" AND DATE(`ctime`) <= ?");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getEndDate()));
		}
		
		sql.append(" GROUP BY createDate ORDER BY createDate DESC");
		
		Long count = dslContext.fetchOne("SELECT COUNT(*) AS num FROM (" + sql.toString() + ") AS c", paramList.toArray()).into(Long.class);
		
		paramList.add(pageable.getOffset());
		paramList.add(pageable.getPageSize());
		
		return new PageImpl<NuanXinDaiUnionSearchResponse>(dslContext.fetch(sql.toString() + " LIMIT ?,?", paramList.toArray()).map(new RecordMapper<Record, NuanXinDaiUnionSearchResponse>() {

			@Override
			public NuanXinDaiUnionSearchResponse map(Record record) {
				NuanXinDaiUnionSearchResponse response = new NuanXinDaiUnionSearchResponse();
				response.setCreateDate(record.get("createDate", Date.class));
				response.setUnionName(record.get("unionName", String.class));
				response.setUnionRequestNum(record.get("unionRequestNum", Integer.class));
				response.setUnionSuccessNum(record.get("unionSuccessNum", Integer.class));
				
				return response;
			}
		}), pageable, count);

	}
	
}
